﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.Sql;
using System.Data.SqlClient;
using CNCSoft.EWinform.ERPApps;
using CNCSoft.EData;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

using System.Threading;
using System.Globalization;

using Common;

namespace CNCSoft.ErpApps.TMS.Accountant
{
    public partial class frmPhieuBaoHocPhi : Form
    {
        DataTable DtTienMatChiTiet;
        DataTable dtKH;
        bool cancelEvent = false;

        public frmPhieuBaoHocPhi()
        {
            // Sets the UI culture to VietNamese (VietNam) / English
            if (GlobalVariable.WSInfo.Language == "Tiếng Việt")
                Thread.CurrentThread.CurrentUICulture = new CultureInfo("vi-VN");
            else if (GlobalVariable.WSInfo.Language == "English")
                Thread.CurrentThread.CurrentUICulture = new CultureInfo("en");

            InitializeComponent();
        }

        private void txtMaNienHoc_TextChanged(object sender, EventArgs e)
        {
            if (cancelEvent) return;
            cancelEvent = true;
            try
            {
                if (txtMaNienHoc.Text == "") return;

                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHoc.Text + "' Order by MaKhoi, STT");

                cboLop.DataSource = dt;
                cboLop.DisplayMember = "TenLop";
                cboLop.ValueMember = "MaLop";
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
            cancelEvent = false;
        }

        private void cboLop_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cancelEvent) return;
            cancelEvent = true;
            try
            {
                if (cboLop.SelectedIndex == -1) return;
                DataTable dtLop = new DataTable();
                SQLHelper.Fill(dtLop, GlobalVariable.WSInfo.ConnectionString, "SELECT MaKhoi, MaHocTap, HOCSINH_LOP.MaHocSinh, HOCSINH_LOP.MaLop, HoLot + ' ' + Ten  As HoTen, " +
                                                        "MaCheDo, MaTrangThai, STT_SoCai, getdate() as NgayVaoLop, '' as MaLopCu " + 
                                                        "FROM HOCSINH_LOP " +
                                                        "JOIN HOCSINH ON HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh " +
                                                        "JOIN DM_LOP ON HOCSINH_LOP.MaLop = DM_LOP.MaLop  " +
                                                        "WHERE HOCSINH_LOP.MaLop = '" + cboLop.SelectedValue + "' " + 
                                                        "AND MaTrangThai = 'SS' " +
                                                        "Order by STT_SoCai, Ten, HoLot ");
                lstHocSinh.DataSource = dtLop;
                lstHocSinh.Refresh();
                //lstHocSinh.DisplayMember = "HoTen";
                //lstHocSinh.ValueMember = "MaHocTap";
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
            cancelEvent = false;
        }

        private void lstHocSinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cancelEvent) return;
            cancelEvent = true;
            try
            {
                if (lstHocSinh.SelectedIndex == -1) return;

                string strKhoi = ((DataTable)lstHocSinh.DataSource).Rows[lstHocSinh.SelectedIndex]["MaKhoi"].ToString();
                string strCheDo = ((DataTable)lstHocSinh.DataSource).Rows[lstHocSinh.SelectedIndex]["MaCheDo"].ToString();
                LoadKeHoach(strKhoi, strCheDo);

                lblHSDangXuLy.Text = lstHocSinh.Text.ToUpper();
                
                int intHSDangChon = gridKeHoach.BindingContext[(DataTable)lstHocSinh.DataSource].Position;
                string strMaHT = ((DataTable)lstHocSinh.DataSource).Rows[intHSDangChon]["MaHocTap"].ToString().Trim();
                
                txtMaDoiTac.Text = strMaHT  ; //lstHocSinh.SelectedValue.ToString();
                txtMaCheDo.Text = strCheDo;

                panel_ChungtuTM.Visible = true;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            cancelEvent = false;
        }

        private void LoadKeHoach(string strMaKhoi, string strMaCheDo)
        {
            try
            {
                dtKH = new DataTable();
                SQLHelper.Fill(dtKH, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_HOCPHI WHERE MaKhoi = '" + strMaKhoi + "' " +
                                                                           "AND LoaiHocPhi = '" + strMaCheDo + "' ORDER BY SoLan");
                gridKeHoach.DataSource = dtKH;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        
        

        private void cmdKHCaLop_Click(object sender, EventArgs e)
        {
            //Insert vao bang TienMat va TienMat_ChiTiet: 
            // So RC = (so HS) x (So lan trong KH thu HP) - bang Master
            // Bang Detail tuy thuoc vao detail cua moi lan thu HP
            string strMaHT;
            string strHoTen;
            string strKhoi;
            string strCheDo;
            string strSoLan ;
            string strInsert1HS_TienMat;
            string strInsert_TienMatChiTiet;
            string strSoChungTu;

            progressBar1.Minimum = 0;
            progressBar1.Maximum = lstHocSinh.ItemCount;
            progressBar1.Value = 0;
            panel_ChungtuTM.Visible = false;

            try
            {
                for (int i = 0; i < lstHocSinh.ItemCount; i++)
                {
                    strMaHT = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaHocTap"].ToString().Trim();
                    strHoTen = ((DataTable)lstHocSinh.DataSource).Rows[i]["HoTen"].ToString().Trim();
                    strKhoi = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaKhoi"].ToString().Trim();
                    strCheDo = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaCheDo"].ToString().Trim();

                    //Kiem tra da lap KH cho lop nay chua?
                    if ( SQLHelper.ExecuteScalar(GlobalVariable.WSInfo.ConnectionString, "SELECT SoChungTu FROM TIENMAT WHERE MaLoaiChungTu = 'KH' AND MaDoiTac ='" +  strMaHT + "'") !=null)
                    {
                        MessageBox.Show("Đã lập kế hoạch thu học phí cho lớp này rồi", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    progressBar1.Value++; 
                    lblHSDangXuLy.Text = strHoTen;
                    lblHSDangXuLy.Refresh();

                    SqlDataReader HV_DataReader = SQLHelper.ExecuteReader(GlobalVariable.WSInfo.ConnectionString,
                                                                          "SELECT MaHocPhi, SoLan, DienGiaiMaHocPhi, convert(Date, NgayDuKienThu, 103) as NgayDuKienThu " + 
                                                                          "FROM DM_HOCPHI  " +
                                                                          "WHERE MaKhoi = '" + strKhoi + "' " +
                                                                          "AND LoaiHocPhi = '" + strCheDo + "'");
                    
                    while (HV_DataReader.Read())
                    {
                        //Tinh toan so chung tu tiep theo (KHyykk)
                        int intSoTiepTheo;
                        string prefixKH_Khoi = "KH" + ((DateTime)(HV_DataReader["NgayDuKienThu"])).Year.ToString().Substring(2, 2) + strKhoi;
                        string MaxOfSoChungTu_KH_KHoi = SQLHelper.ExecuteScalar(GlobalVariable.WSInfo.ConnectionString, "SELECT isnull(Max(SoChungTu),0) as MaxOfSoChungTu_KH FROM TIENMAT WHERE left(SoChungTu,6) = '" + prefixKH_Khoi + "' ").ToString();
                        if (MaxOfSoChungTu_KH_KHoi == "0")
                        {
                            MaxOfSoChungTu_KH_KHoi = prefixKH_Khoi + "@00000";
                        }
                        intSoTiepTheo = Convert.ToInt16(MaxOfSoChungTu_KH_KHoi.Substring(7, 5)) + 1;
                        strSoChungTu = prefixKH_Khoi + "@" + intSoTiepTheo.ToString("00000");
                        //MessageBox.Show("So tiep: " + strSoChungTu);

                        //TienMat 
                        strSoLan = HV_DataReader["SoLan"].ToString().Trim();
                        //strSoChungTu = "KH_" + strMaHT + "_" + strSoLan + '_' + HV_DataReader["MaHocPhi"];
                        strInsert1HS_TienMat = "INSERT INTO TIENMAT (SoChungTu, MaLoaiChungTu, NgayChungTu, MaDoiTac, HoTen, DienGiai, TongTien, NgayDuKienThu, MaLop, MaCheDo, MaNienHoc, MaHocPhi, SoLanTrenInvoice) " +
                                                              "VALUES ('" + strSoChungTu + "', " +
                                                                     "'KH', " +
                                                                     "'" + HV_DataReader["NgayDuKienThu"] + "', " +
                                                                     "'" + strMaHT + "', " +
                                                                     "N'" + strHoTen + "', " +
                                                                     "N'" + HV_DataReader["DienGiaiMaHocPhi"] + "', " +
                                                                     "0, " +
                                                                     "'" + HV_DataReader["NgayDuKienThu"] + "', " +
                                                                     "'" + cboLop.SelectedValue + "' ," +
                                                                     "'" + strCheDo + "' ," +
                                                                     "'" +  txtMaNienHoc.Text +"', " +
                                                                     "'" + HV_DataReader["MaHocPhi"] +  "', " +
                                                                     HV_DataReader["SoLan"] +
                                                               " )";
                        SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strInsert1HS_TienMat);

                        //Tienmat_Chitiet
                        strInsert_TienMatChiTiet = "INSERT INTO TIENMAT_CHITIET (SoChungTu, TaiKhoanCo, MaTieuKhoanCo1, DienGiaiChiTiet, SoLuong, DonGia, SoTien, " +
                                                                                "TaiKhoanNo, MaTieuKhoanNo1, MaHocPhi) " +
                                                                        "SELECT '" + strSoChungTu + "', " +
                                                                                "MaTaiKhoan, " +
                                                                                "'" + strMaHT + "', " +
                                                                                "DienGiai, " +
                                                                                "SoLuong, DonGia, SoLuong*DonGia as SoTien, " +
                                                                                "MaTaiKhoanDoiUng, " +
                                                                                "'" + strMaHT + "', " +
                                                                                "'" + HV_DataReader["MaHocPhi"] + "' " +
                                                                         "FROM DM_HOCPHI_CHITIET  " +
                                                                         "WHERE DM_HOCPHI_CHITIET.MaHocPhi = '" + HV_DataReader["MaHocPhi"] + "' " +

                                                                         "AND DM_HOCPHI_CHITIET.MaKhoi ='" + strKhoi + "' ";
                        
                        SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "SET NOCOUNT ON");

                        //MessageBox.Show("Chuan bi den detail: " + strInsert_TienMatChiTiet);
                        
                        SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strInsert_TienMatChiTiet);

                        //Update soluong tu DM_DINHNGHIAHOCPHI
                        string sqlUpdateSoLuongDK = "update TIENMAT_CHITIET set SoLuong = SoLuongDK, SoTien = SoLuongDK * isnull(DonGia,0)  " +
                                            "from TIENMAT_CHITIET D " +
                                            "join TIENMAT M ON M.SoChungTu = D.SoChungtu " +
                                            "join (select MaHocTap, SoTaiKhoan, SoLuong as SoLuongDK from HOCSINH_DANGKYHOCPHI where MaHocTap= '" + strMaHT + "' and SoLuong>0) DK  " +
                                            "on D.TaiKhoanCO = DK.SoTaiKhoan " +
                                            "where D.SoChungTu = '" + strSoChungTu + "' ";
                                            ///////"where M.MaDoitac = '" + strMaHT + "' ";
                        SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, sqlUpdateSoLuongDK);

                        //Update TongTien cua TIENMAT
                        SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, 
                                           "UPDATE TIENMAT SET TongTien = (select sum(sotien) from tienmat_chitiet where sochungtu = '" +  strSoChungTu + "') " +
                                           "WHERE SoChungTu = '" + strSoChungTu + "'"  );

                    }
                }  //for

                //MessageBox.Show("Thanh cong, bay gio can update so luong DK");
                lblHSDangXuLy.Text = "Thành công";

            }
            catch (Exception ex)
            {
                progressBar1.Value = 0;
                if (ex.Message.IndexOf("duplicate key") >0)
                    MessageBox.Show("Đã lập kế hoạch thu học phí cho lớp này rồi", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                else
                    MessageBox.Show( ex.Message);
            }



        }

        private void frmPhieuBaoHocPhi_Load(object sender, EventArgs e)
        {
            txtMaNienHoc.Text = GlobalVariable.WSInfo.getAppProperty("MaNienHocThuHocPhi"); //GlobalVariable.WSInfo.getAppProperty("MaNienHocDefault");
            lblHSDangXuLy.Text = "";
        }

        private void GetDataTienMatChiTiet(string MaHocTap, string MaHocPhi)
        {
            if (cancelEvent) return;
            cancelEvent = true;

            try
            {
                DtTienMatChiTiet = new DataTable();
                
                string strSELECT = "SELECT D.* FROM TIENMAT_CHITIET D JOIN TIENMAT M ON D.SoChungTu = M.SoChungTu WHERE MaLoaiChungTu ='KH' and MaDoiTac = '" + MaHocTap + "' AND M.MaHocPhi = '" + MaHocPhi + "'";
                SQLHelper.Fill(DtTienMatChiTiet, GlobalVariable.WSInfo.ConnectionString, strSELECT);
                gridTienMatChiTiet.DataSource = DtTienMatChiTiet;
                
                if (DtTienMatChiTiet.Rows.Count>0)
                    txtSoChungTu.Text = DtTienMatChiTiet.Rows[0]["SoChungTu"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            cancelEvent = false;
        }


        int intCurrRowOfdtKH;  //Luu lai vi tri cua row da chon trong dtKH

        private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            try
            {
                intCurrRowOfdtKH = e.FocusedRowHandle;

                int intHSDangChon = gridKeHoach.BindingContext[(DataTable)lstHocSinh.DataSource].Position;
                string strMaHT = ((DataTable)lstHocSinh.DataSource).Rows[intHSDangChon]["MaHocTap"].ToString().Trim();
                string strMaHocPhi = dtKH.Rows[e.FocusedRowHandle]["MaHocPhi"].ToString();
                GetDataTienMatChiTiet(strMaHT, strMaHocPhi);
                cndLapKHTheoDotHocPhi.Text = "Lập KH " + dtKH.Rows[e.FocusedRowHandle]["DienGiaiMaHocPhi"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void cmdCapNhat_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection cnn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);

                string sqlInsert, sqlUpdate, sqlDelete;
                sqlInsert = "INSERT INTO TIENMAT_CHITIET(SoChungTu, TaiKhoanNO, MaTieuKhoanNo1, DienGiaiChiTiet, SoLuong, DonGia, SoTien, TaiKhoanCO, MaTieuKhoanCo1) " +
                                              " VALUES(@pSoChungTu, @pTaiKhoanNO, @pMaTieuKhoanNo1, @pDienGiaiChiTiet, @pSoLuong, @pDonGia, @pSoTien, @pTaiKhoanCO, @pMaTieuKhoanCo1 " +
                                              ") ";
                sqlUpdate = "UPDATE TIENMAT_CHITIET SET TaiKhoanNO=@pTaiKhoanNO, MaTieuKhoanNo1=@pMaTieuKhoanNo1, DienGiaiChiTiet=@pDienGiaiChiTiet, SoLuong=@pSoLuong, DonGia=@pDonGia, SoTien=@pSoTien, TaiKhoanCO= @pTaiKhoanCO, MaTieuKhoanCo1=@pMaTieuKhoanCo1 " +
                                   " WHERE ID=@pID";

                sqlDelete = "DELETE FROM TIENMAT_CHITIET WHERE ID=@pID";

                SqlParameter[] pInsert = new SqlParameter[9];
                SqlParameter[] pUpdate = new SqlParameter[9];
                SqlParameter[] pDelete = new SqlParameter[1];

                pInsert[0] = new SqlParameter("@pSoChungTu", SqlDbType.VarChar, 25, "SoChungTu");
                pInsert[1] = new SqlParameter("@pTaiKhoanNO", SqlDbType.VarChar, 25, "TaiKhoanNO");
                pInsert[2] = new SqlParameter("@pMaTieuKhoanNo1", SqlDbType.VarChar, 50, "MaTieuKhoanNo1");
                pInsert[3] = new SqlParameter("@pDienGiaiChiTiet", SqlDbType.NVarChar, 500, "DienGiaiChiTiet");
                pInsert[4] = new SqlParameter("@pSoLuong", SqlDbType.Money, 18, "SoLuong");
                pInsert[5] = new SqlParameter("@pDonGia", SqlDbType.Money, 18, "DonGia");
                pInsert[6] = new SqlParameter("@pSoTien", SqlDbType.Money, 18, "SoTien");
                pInsert[7] = new SqlParameter("@pTaiKhoanCO", SqlDbType.VarChar, 25, "TaiKhoanCO");
                pInsert[8] = new SqlParameter("@pMaTieuKhoanCo1", SqlDbType.VarChar, 50, "MaTieuKhoanCo1");

                pUpdate[0] = new SqlParameter("@pTaiKhoanNO", SqlDbType.VarChar, 25, "TaiKhoanNO");
                pUpdate[1] = new SqlParameter("@pMaTieuKhoanNo1", SqlDbType.VarChar, 50, "MaTieuKhoanNo1");
                pUpdate[2] = new SqlParameter("@pDienGiaiChiTiet", SqlDbType.NVarChar, 500, "DienGiaiChiTiet");
                pUpdate[3] = new SqlParameter("@pSoLuong", SqlDbType.Money, 18, "SoLuong");
                pUpdate[4] = new SqlParameter("@pDonGia", SqlDbType.Money, 18, "DonGia");
                pUpdate[5] = new SqlParameter("@pSoTien", SqlDbType.Money, 18, "SoTien");
                pUpdate[6] = new SqlParameter("@pTaiKhoanCO", SqlDbType.VarChar, 25, "TaiKhoanCO");
                pUpdate[7] = new SqlParameter("@pMaTieuKhoanCo1", SqlDbType.VarChar, 50, "MaTieuKhoanCo1");
                pUpdate[8] = new SqlParameter("@pID", SqlDbType.Int, 18, "ID");

                pDelete[0] = new SqlParameter("@pID", SqlDbType.VarChar, 50, "ID");

                SqlCommand cmdInsert = new SqlCommand(sqlInsert, cnn);
                SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, cnn);
                SqlCommand cmdDelete = new SqlCommand(sqlDelete, cnn);

                cmdInsert.Parameters.AddRange(pInsert);
                cmdUpdate.Parameters.AddRange(pUpdate);
                cmdDelete.Parameters.AddRange(pDelete);

                SqlDataAdapter da = new SqlDataAdapter();
                da.InsertCommand = cmdInsert;
                da.UpdateCommand = cmdUpdate;
                da.DeleteCommand = cmdDelete;
                try
                {
                    da.Update(DtTienMatChiTiet);
                    DtTienMatChiTiet.AcceptChanges();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                //Update TongTien cua TIENMAT
                SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString,
                                   "UPDATE TIENMAT SET TongTien = (select sum(sotien) from tienmat_chitiet where sochungtu = '" + DtTienMatChiTiet.Rows[intCurrRowOfdtKH]["SoChungTu"].ToString() + "') " +
                                   "WHERE SoChungTu = '" + DtTienMatChiTiet.Rows[intCurrRowOfdtKH]["SoChungTu"].ToString() + "'");
 

                //Load lai
                string strMaHT = ((DataTable)lstHocSinh.DataSource).Rows[lstHocSinh.SelectedIndex]["MaHocTap"].ToString().Trim();
                string strMaHocPhi = dtKH.Rows[intCurrRowOfdtKH]["MaHocPhi"].ToString();
                GetDataTienMatChiTiet(strMaHT, strMaHocPhi);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gridView2_CustomRowCellEdit(object sender, DevExpress.XtraGrid.Views.Grid.CustomRowCellEditEventArgs e)
        {
            /*
            if (e.CellValue == null || e.GetType().Name != System.TypeCode.Decimal.ToString())
                return;

            DtTienMatChiTiet.Rows[e.RowHandle]["SoTien"] = Convert.ToDecimal(DtTienMatChiTiet.Rows[e.RowHandle]["SoLuong"]) * Convert.ToDecimal(DtTienMatChiTiet.Rows[e.RowHandle]["DonGia"]);
             */
        }

        private void cmdBoQua_Click(object sender, EventArgs e)
        {
            //Load lai
            string strMaHT = ((DataTable)lstHocSinh.DataSource).Rows[lstHocSinh.SelectedIndex]["MaHocTap"].ToString().Trim();
            string strMaHocPhi = dtKH.Rows[intCurrRowOfdtKH]["MaHocPhi"].ToString();
            GetDataTienMatChiTiet(strMaHT, strMaHocPhi);
        }

        private void cboLop_Validated(object sender, EventArgs e)
        {
            
        }

        private void cmdIn_Click(object sender, EventArgs e)
        {
            try
            {

                cmdIn.Enabled = false;

                //Load report
                string strMaHocPhi = dtKH.Rows[intCurrRowOfdtKH]["MaHocPhi"].ToString();
                string strReportName = "";
                ReportDocument rpt = new ReportDocument();

                if ((int)dtKH.Rows[intCurrRowOfdtKH]["SoLan"] == 1)
                    strReportName = Application.StartupPath + "\\Report\\sp_Print_PhieuBaoHocPhi_CuChuoi.rpt";
                else
                    strReportName = Application.StartupPath + "\\Report\\sp_Print_PhieuBaoHocPhi_THANG.rpt";

                rpt.Load(strReportName);
                rpt.Refresh();

                //Set connection
                PublicFunction.SetDBLogonForReport(rpt);

                //Set parameter
                PublicFunction.SetParameter(rpt, "@pMaDoiTac", lstHocSinh.SelectedValue);
                PublicFunction.SetParameter(rpt, "@pMaHocPhi", strMaHocPhi);
                PublicFunction.SetParameter(rpt, "@pMaLop", "");
                //PublicFunction.SetParameter(rpt, "pNguoiLapBieu", GlobalVariable.WSInfo.UserName);
                //PublicFunction.SetParameter(rpt, "@pFieldGroup", "");
                //PublicFunction.SetParameter(rpt, "pMoRongDong", false);

                //View report
                frmPrintPreview frmPP = new frmPrintPreview(rpt);
                frmPP.ShowDialog();
                frmPP.Dispose();

                rpt.Dispose();
                cmdIn.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gridKeHoach_Click(object sender, EventArgs e)
        {

        }

        private void gridView2_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            if (e.Value != null && ((e.Column.FieldName.ToUpper() == "SOLUONG") || (e.Column.FieldName.ToUpper() == "DONGIA")))
                if (DtTienMatChiTiet.Rows[e.RowHandle]["SoLuong"] != DBNull.Value && DtTienMatChiTiet.Rows[e.RowHandle]["DonGia"]!= DBNull.Value)
                    DtTienMatChiTiet.Rows[e.RowHandle]["SoTien"] = Convert.ToDecimal(DtTienMatChiTiet.Rows[e.RowHandle]["SoLuong"]) * Convert.ToDecimal(DtTienMatChiTiet.Rows[e.RowHandle]["DonGia"]);
        }

        private void cmdInNguyenLop_Click(object sender, EventArgs e)
        {
            try
            {
                cmdIn.Enabled = false;

                //Load report
                string strMaHocPhi = dtKH.Rows[intCurrRowOfdtKH]["MaHocPhi"].ToString();
                string strReportName = "";
                ReportDocument rpt = new ReportDocument();
                
                if ((int)dtKH.Rows[intCurrRowOfdtKH]["SoLan"] == 1)
                    strReportName = Application.StartupPath + "\\Report\\sp_Print_PhieuBaoHocPhi_CuChuoi.rpt";
                else
                    strReportName = Application.StartupPath + "\\Report\\sp_Print_PhieuBaoHocPhi_THANG.rpt";
                
                rpt.Load(strReportName);
                rpt.Refresh();

                //Set connection
                PublicFunction.SetDBLogonForReport(rpt);

                //Set parameter
                PublicFunction.SetParameter(rpt, "@pMaDoiTac", "");
                PublicFunction.SetParameter(rpt, "@pMaHocPhi", strMaHocPhi);
                PublicFunction.SetParameter(rpt, "@pMaLop", cboLop.SelectedValue);
                //PublicFunction.SetParameter(rpt, "pNguoiLapBieu", GlobalVariable.WSInfo.UserName);
                //PublicFunction.SetParameter(rpt, "@pFieldGroup", "");
                //PublicFunction.SetParameter(rpt, "pMoRongDong", false);

                //View report
                frmPrintPreview frmPP = new frmPrintPreview(rpt);
                frmPP.ShowDialog();
                frmPP.Dispose();

                rpt.Dispose();
                cmdIn.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void cmdXoaLamLai_Click(object sender, EventArgs e)
        {
            //Kiem tra da thu tien mat chua?
            if (SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "SELECT SoChungTu FROM TIENMAT WHERE SoChungTu IN (SELECT SoInVoice FROM TIENMAT)") > 0)
            {
                MessageBox.Show("Lớp này đã có phiếu thu tiền mặt, không xóa kế hoạch được.", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //Xoa
            try
            {
                SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "DELETE FROM TIENMAT_CHITIET WHERE SoChungTu In (SELECT SoChungTu FROM TIENMAT WHERE MaLop = '" + cboLop.SelectedValue + "' AND MaLoaiChungTu = 'KH') ");
                SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "DELETE FROM TIENMAT WHERE MaLop = '" + cboLop.SelectedValue + "' AND MaLoaiChungTu = 'KH' ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("OK! Đã xóa toàn bộ kế hoạch thu học phí của lớp này", "Xóa KH thu học phí",  MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        private void cmdKH_HSDangChon_Click(object sender, EventArgs e)
        {
            string strMaHT;
            string strHoTen;
            string strKhoi;
            string strCheDo;
            string strSoLan;
            string strInsert1HS_TienMat;
            string strInsert_TienMatChiTiet;
            string strSoChungTu;

            panel_ChungtuTM.Visible = false;

            string strMaHocPhi = dtKH.Rows[intCurrRowOfdtKH]["MaHocPhi"].ToString();

            BindingManagerBase bmb = this.BindingContext[lstHocSinh.DataSource];
            int i;
            
            i = bmb.Position;  //Hoc sinh dang chon
            strMaHT = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaHocTap"].ToString().Trim();
            strHoTen = ((DataTable)lstHocSinh.DataSource).Rows[i]["HoTen"].ToString().Trim();
            strKhoi = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaKhoi"].ToString().Trim();
            strCheDo = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaCheDo"].ToString().Trim();

            //Truong hop 1 hoc sinh => khong quan tam den Thu tien hay chua. Xoa, lap KH lai
            if (XoaChungTuKeHoachTheoDotHocPhi_HocSinh(strMaHT, strMaHocPhi))
            {
                //Xoa thanh cong, Bat dau lap lai KH
                try
                {
                        lblHSDangXuLy.Text = strHoTen;
                        lblHSDangXuLy.Refresh();

                        SqlDataReader HV_DataReader = SQLHelper.ExecuteReader(GlobalVariable.WSInfo.ConnectionString,
                                                                              "SELECT MaHocPhi, SoLan, DienGiaiMaHocPhi, convert(Date, NgayDuKienThu, 103) as NgayDuKienThu " +
                                                                              "FROM DM_HOCPHI  " +
                                                                              "WHERE MaKhoi = '" + strKhoi + "' " +
                                                                              "AND LoaiHocPhi = '" + strCheDo + "' " +
                                                                              "AND MaHocPhi = '" + strMaHocPhi + "'");

                        while (HV_DataReader.Read())
                        {
                            //Tinh toan so chung tu tiep theo (KHyykk)
                            int intSoTiepTheo;
                            string prefixKH_Khoi = "KH" + ((DateTime)(HV_DataReader["NgayDuKienThu"])).Year.ToString().Substring(2, 2) + strKhoi;
                            string MaxOfSoChungTu_KH_KHoi = SQLHelper.ExecuteScalar(GlobalVariable.WSInfo.ConnectionString, "SELECT isnull(Max(SoChungTu),0) as MaxOfSoChungTu_KH FROM TIENMAT WHERE left(SoChungTu,6) = '" + prefixKH_Khoi + "' ").ToString();
                            if (MaxOfSoChungTu_KH_KHoi == "0")
                            {
                                MaxOfSoChungTu_KH_KHoi = prefixKH_Khoi + "@00000";
                            }
                            intSoTiepTheo = Convert.ToInt16(MaxOfSoChungTu_KH_KHoi.Substring(7, 5)) + 1;
                            strSoChungTu = prefixKH_Khoi + "@" + intSoTiepTheo.ToString("00000");
                            //MessageBox.Show("So tiep: " + strSoChungTu);

                            //TienMat 
                            strSoLan = HV_DataReader["SoLan"].ToString().Trim();
                            //strSoChungTu = "KH_" + strMaHT + "_" + strSoLan + '_' + HV_DataReader["MaHocPhi"];
                            strInsert1HS_TienMat = "INSERT INTO TIENMAT (SoChungTu, MaLoaiChungTu, NgayChungTu, MaDoiTac, HoTen, DienGiai, TongTien, NgayDuKienThu, MaLop, MaCheDo, MaNienHoc, MaHocPhi, SoLanTrenInvoice) " +
                                                                  "VALUES ('" + strSoChungTu + "', " +
                                                                         "'KH', " +
                                                                         "'" + HV_DataReader["NgayDuKienThu"] + "', " +
                                                                         "'" + strMaHT + "', " +
                                                                         "N'" + strHoTen + "', " +
                                                                         "N'" + HV_DataReader["DienGiaiMaHocPhi"] + "', " +
                                                                         "0, " +
                                                                         "'" + HV_DataReader["NgayDuKienThu"] + "', " +
                                                                         "'" + cboLop.SelectedValue + "' ," +
                                                                         "'" + strCheDo + "' ," +
                                                                         "'" + txtMaNienHoc.Text + "', " +
                                                                         "'" + HV_DataReader["MaHocPhi"] + "', " +
                                                                         HV_DataReader["SoLan"] +
                                                                   " )";
                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strInsert1HS_TienMat);

                            //Tienmat_Chitiet
                            strInsert_TienMatChiTiet = "INSERT INTO TIENMAT_CHITIET (SoChungTu, TaiKhoanCo, MaTieuKhoanCo1, DienGiaiChiTiet, SoLuong, DonGia, SoTien, " +
                                                                                    "TaiKhoanNo, MaTieuKhoanNo1, MaHocPhi) " +
                                                                            "SELECT '" + strSoChungTu + "', " +
                                                                                    "MaTaiKhoan, " +
                                                                                    "'" + strMaHT + "', " +
                                                                                    "DienGiai, " +
                                                                                    "SoLuong, DonGia, SoLuong*DonGia as SoTien, " +
                                                                                    "MaTaiKhoanDoiUng, " +
                                                                                    "'" + strMaHT + "', " +
                                                                                    "'" + HV_DataReader["MaHocPhi"] + "' " +
                                                                             "FROM DM_HOCPHI_CHITIET  " +
                                                                             "WHERE DM_HOCPHI_CHITIET.MaHocPhi = '" + HV_DataReader["MaHocPhi"] + "' " +

                                                                             "AND DM_HOCPHI_CHITIET.MaKhoi ='" + strKhoi + "' ";

                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "SET NOCOUNT ON");

                            //MessageBox.Show("Chuan bi den detail: " + strInsert_TienMatChiTiet);

                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strInsert_TienMatChiTiet);

                            //Update soluong tu DM_DINHNGHIAHOCPHI
                            string sqlUpdateSoLuongDK = "update TIENMAT_CHITIET set SoLuong = SoLuongDK, SoTien = SoLuongDK * isnull(DonGia,0)  " +
                                                "from TIENMAT_CHITIET D " +
                                                "join TIENMAT M ON M.SoChungTu = D.SoChungtu " +
                                                "join (select MaHocTap, SoTaiKhoan, SoLuong as SoLuongDK from HOCSINH_DANGKYHOCPHI where MaHocTap= '" + strMaHT + "' and SoLuong>0) DK  " +
                                                "on D.TaiKhoanCO = DK.SoTaiKhoan " +
                                                "where D.SoChungTu = '" + strSoChungTu + "' ";
                                                //////"where M.MaDoitac = '" + strMaHT + "' ";
                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, sqlUpdateSoLuongDK);

                            //Update TongTien cua TIENMAT
                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString,
                                               "UPDATE TIENMAT SET TongTien = (select sum(sotien) from tienmat_chitiet where sochungtu = '" + strSoChungTu + "') " +
                                               "WHERE SoChungTu = '" + strSoChungTu + "'");

                        }

                    //MessageBox.Show("Thanh cong, bay gio can update so luong DK");
                    lblHSDangXuLy.Text = "Thành công";

                }
                catch (Exception ex)
                {
                    progressBar1.Value = 0;
                    if (ex.Message.IndexOf("duplicate key") > 0)
                        MessageBox.Show("Đã lập kế hoạch thu học phí cho lớp này rồi", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    else
                        MessageBox.Show(ex.Message);
                }

            }

        }



        private Boolean XoaChungTuKeHoachTheoDotHpocPhi(string MaLop, string MaHocPhi)
        {
            try
            {
                //Kiem tra da thu tien mat chua? (cho dot hoc phi cua MaHocPhi)
                string strSQL_SELE = "select SoChungTu from TIENMAT where SoChungTu in (SELECT SoInvoice FROM TIENMAT WHERE MaHocPhi = '" + MaHocPhi + "' AND MaLop = '" + MaLop + "')";

                if (SQLHelper.ExecuteScalar(GlobalVariable.WSInfo.ConnectionString, strSQL_SELE) != null)
                {
                    MessageBox.Show("Lớp này đã có phiếu thu tiền mặt cho đợt học phí này rồi, không xóa kế hoạch được.", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }

            //Xoa
            string strSQL_DELETE_TIENMAT = "DELETE FROM TIENMAT WHERE MaLop = '" + MaLop + "' AND MaLoaiChungTu = 'KH' " +
                                           "AND MaHocPhi = '" + MaHocPhi + "'";
            string strSELE_TIENMAT = "SELECT SoChungTu FROM TIENMAT WHERE MaLop = '" + MaLop + "' AND MaLoaiChungTu = 'KH' " +
                                           "AND MaHocPhi = '" + MaHocPhi + "'"; ;
            string strSQL_DELETE_TIENMAT_CHITIET = "DELETE FROM TIENMAT_CHITIET " +
                                                    "WHERE SoChungTu In  (" + strSELE_TIENMAT + ")";
            try
            {
                SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strSQL_DELETE_TIENMAT_CHITIET);
                SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strSQL_DELETE_TIENMAT);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }

            return true;
        }

        //Xoa ke hoach cua 1 hoc sinh, neu co hoan hoc phi thi du co thu roi cung cho xoa
        private Boolean XoaChungTuKeHoachTheoDotHocPhi_HocSinh(string MaDoiTac, string MaHocPhi)
        {
            //Xoa
            string strSQL_DELETE_TIENMAT = "DELETE FROM TIENMAT WHERE MaDoiTac = '" + MaDoiTac + "' AND MaLoaiChungTu = 'KH' " +
                                           "AND MaHocPhi = '" + MaHocPhi + "'";
            string strSELE_TIENMAT = "SELECT SoChungTu FROM TIENMAT WHERE MaDoiTac = '" + MaDoiTac + "' AND MaLoaiChungTu = 'KH' " +
                                           "AND MaHocPhi = '" + MaHocPhi + "'"; ;
            string strSQL_DELETE_TIENMAT_CHITIET = "DELETE FROM TIENMAT_CHITIET " +
                                                    "WHERE SoChungTu In  (" + strSELE_TIENMAT + ")";
            try
            {
                SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strSQL_DELETE_TIENMAT_CHITIET);
                SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strSQL_DELETE_TIENMAT);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }

            return true;
        }

        private void cndLapKHTheoDotHocPhi_Click(object sender, EventArgs e)
        {
            string strMaHT;
            string strHoTen;
            string strKhoi;
            string strCheDo;
            string strSoLan;
            string strInsert1HS_TienMat;
            string strInsert_TienMatChiTiet;
            string strSoChungTu;

            progressBar1.Minimum = 0;
            progressBar1.Maximum = lstHocSinh.ItemCount;
            progressBar1.Value = 0;
            panel_ChungtuTM.Visible = false;

            string strMaHocPhi = dtKH.Rows[intCurrRowOfdtKH]["MaHocPhi"].ToString();
            if (XoaChungTuKeHoachTheoDotHpocPhi(cboLop.SelectedValue.ToString(), strMaHocPhi))
            {
                //Xoa thanh cong, Bat dau lap lai KH
                try
                {
                    for (int i = 0; i < lstHocSinh.ItemCount; i++)
                    {
                        strMaHT = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaHocTap"].ToString().Trim();
                        strHoTen = ((DataTable)lstHocSinh.DataSource).Rows[i]["HoTen"].ToString().Trim();
                        strKhoi = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaKhoi"].ToString().Trim();
                        strCheDo = ((DataTable)lstHocSinh.DataSource).Rows[i]["MaCheDo"].ToString().Trim();

                        progressBar1.Value++;
                        lblHSDangXuLy.Text = strHoTen;
                        lblHSDangXuLy.Refresh();

                        SqlDataReader HV_DataReader = SQLHelper.ExecuteReader(GlobalVariable.WSInfo.ConnectionString,
                                                                              "SELECT MaHocPhi, SoLan, DienGiaiMaHocPhi, convert(Date, NgayDuKienThu, 103) as NgayDuKienThu " +
                                                                              "FROM DM_HOCPHI  " +
                                                                              "WHERE MaKhoi = '" + strKhoi + "' " +
                                                                              "AND LoaiHocPhi = '" + strCheDo + "' " +
                                                                              "AND MaHocPhi = '" + strMaHocPhi + "'");

                        while (HV_DataReader.Read())
                        {
                            //Tinh toan so chung tu tiep theo (KHyykk)
                            int intSoTiepTheo;
                            string prefixKH_Khoi = "KH" + ((DateTime)(HV_DataReader["NgayDuKienThu"])).Year.ToString().Substring(2, 2) + strKhoi;
                            string MaxOfSoChungTu_KH_KHoi = SQLHelper.ExecuteScalar(GlobalVariable.WSInfo.ConnectionString, "SELECT isnull(Max(SoChungTu),0) as MaxOfSoChungTu_KH FROM TIENMAT WHERE left(SoChungTu,6) = '" + prefixKH_Khoi + "' ").ToString();
                            if (MaxOfSoChungTu_KH_KHoi == "0")
                            {
                                MaxOfSoChungTu_KH_KHoi = prefixKH_Khoi + "@00000";
                            }
                            intSoTiepTheo = Convert.ToInt16(MaxOfSoChungTu_KH_KHoi.Substring(7, 5)) + 1;
                            strSoChungTu = prefixKH_Khoi + "@" + intSoTiepTheo.ToString("00000");
                            //MessageBox.Show("So tiep: " + strSoChungTu);

                            //TienMat 
                            strSoLan = HV_DataReader["SoLan"].ToString().Trim();
                            //strSoChungTu = "KH_" + strMaHT + "_" + strSoLan + '_' + HV_DataReader["MaHocPhi"];
                            strInsert1HS_TienMat = "INSERT INTO TIENMAT (SoChungTu, MaLoaiChungTu, NgayChungTu, MaDoiTac, HoTen, DienGiai, TongTien, NgayDuKienThu, MaLop, MaCheDo, MaNienHoc, MaHocPhi, SoLanTrenInvoice) " +
                                                                  "VALUES ('" + strSoChungTu + "', " +
                                                                         "'KH', " +
                                                                         "'" + HV_DataReader["NgayDuKienThu"] + "', " +
                                                                         "'" + strMaHT + "', " +
                                                                         "N'" + strHoTen + "', " +
                                                                         "N'" + HV_DataReader["DienGiaiMaHocPhi"] + "', " +
                                                                         "0, " +
                                                                         "'" + HV_DataReader["NgayDuKienThu"] + "', " +
                                                                         "'" + cboLop.SelectedValue + "' ," +
                                                                         "'" + strCheDo + "' ," +
                                                                         "'" + txtMaNienHoc.Text + "', " +
                                                                         "'" + HV_DataReader["MaHocPhi"] + "', " +
                                                                         HV_DataReader["SoLan"] +
                                                                   " )";
                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strInsert1HS_TienMat);

                            //Tienmat_Chitiet
                            strInsert_TienMatChiTiet = "INSERT INTO TIENMAT_CHITIET (SoChungTu, TaiKhoanCo, MaTieuKhoanCo1, DienGiaiChiTiet, SoLuong, DonGia, SoTien, " +
                                                                                    "TaiKhoanNo, MaTieuKhoanNo1, MaHocPhi) " +
                                                                            "SELECT '" + strSoChungTu + "', " +
                                                                                    "MaTaiKhoan, " +
                                                                                    "'" + strMaHT + "', " +
                                                                                    "DienGiai, " +
                                                                                    "SoLuong, DonGia, SoLuong*DonGia as SoTien, " +
                                                                                    "MaTaiKhoanDoiUng, " +
                                                                                    "'" + strMaHT + "', " +
                                                                                    "'" + HV_DataReader["MaHocPhi"] + "' " +
                                                                             "FROM DM_HOCPHI_CHITIET  " +
                                                                             "WHERE DM_HOCPHI_CHITIET.MaHocPhi = '" + HV_DataReader["MaHocPhi"] + "' " +

                                                                             "AND DM_HOCPHI_CHITIET.MaKhoi ='" + strKhoi + "' ";

                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "SET NOCOUNT ON");

                            //MessageBox.Show("Chuan bi den detail: " + strInsert_TienMatChiTiet);

                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, strInsert_TienMatChiTiet);

                            //Update soluong tu DM_DINHNGHIAHOCPHI
                            string sqlUpdateSoLuongDK = "update TIENMAT_CHITIET set SoLuong = SoLuongDK, SoTien = SoLuongDK * isnull(DonGia,0)  " +
                                                "from TIENMAT_CHITIET D " +
                                                "join TIENMAT M ON M.SoChungTu = D.SoChungtu " +
                                                "join (select MaHocTap, SoTaiKhoan, SoLuong as SoLuongDK from HOCSINH_DANGKYHOCPHI where MaHocTap= '" + strMaHT + "' and SoLuong>0) DK  " +
                                                "on D.TaiKhoanCO = DK.SoTaiKhoan " +
                                                "where D.SoChungTu = '" + strSoChungTu + "' ";
                                                                 //"where M.MaDoitac = '" + strMaHT + "' ";
                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, sqlUpdateSoLuongDK);

                            //Update TongTien cua TIENMAT
                            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString,
                                               "UPDATE TIENMAT SET TongTien = (select sum(sotien) from tienmat_chitiet where sochungtu = '" + strSoChungTu + "') " +
                                               "WHERE SoChungTu = '" + strSoChungTu + "'");

                        }
                    }  //for

                    //MessageBox.Show("Thanh cong, bay gio can update so luong DK");
                    lblHSDangXuLy.Text = "Thành công";

                }
                catch (Exception ex)
                {
                    progressBar1.Value = 0;
                    if (ex.Message.IndexOf("duplicate key") > 0)
                        MessageBox.Show("Đã lập kế hoạch thu học phí cho lớp này rồi", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    else
                        MessageBox.Show(ex.Message);
                }

            }

        }

       

      

        


    }
}
